package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import model.Biologist;
import interfaces.IBiologist;

public class CBiologist implements IBiologist {
  private CConnection db;
  
  public CBiologist(CConnection db) {
	this.db = db;
  }

  @Override
  public boolean create(Biologist b) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
	int i = s.executeUpdate("INSERT INTO Biologist VALUES ('" + b.getPass_number() + "', '" + b.getName() + "', '"
            + b.getJoin_year() + "', '" + b.getCountry() + "', " + b.getContributions() + ")");
	return i > 0;
  }

  @Override
  public boolean update(Biologist b) throws SQLException, ClassNotFoundException {
	Statement s = db.getStatement();
	int i = s.executeUpdate("UPDATE Biologist SET Name = '" + b.getName() + "', Joined_Year = '"
        + b.getJoin_year() + "', Country = '" + b.getCountry() + "', Contributions = " + b.getContributions() + " WHERE Passport_Number = '" + b.getPass_number()+"'");
	return i > 0;
  }

  @Override
  public boolean delete(Biologist b) throws SQLException, ClassNotFoundException {
	Statement s = db.getStatement();
	int i = s.executeUpdate("DELETE FROM Biologist WHERE Passport_Number = '" + b.getPass_number() + "'");
	return i > 0;
  }

  @Override
  public ArrayList<Biologist> retrieveByPassport(String passport) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    ResultSet res = s.executeQuery("select * from biologist where Passport_number = " + passport);
    ArrayList<Biologist> bio = new ArrayList<Biologist>();
    
    while(res.next()) {
      Biologist b = new Biologist();
      b.setPass_number(res.getString("Passport_number"));
      b.setName(res.getString("Name"));
      b.setContributions(res.getInt("contributions"));
      b.setCountry(res.getString("Country"));
      b.setJoin_year(res.getDate("Joined_Year"));
      bio.add(b);
    }
    
    return bio;
  }

  @Override
  public ArrayList<Biologist> retrieveAll() throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    ResultSet res = s.executeQuery("select * from biologist");

    ArrayList<Biologist> bio = new ArrayList<Biologist>();
	    
    while(res.next()) {
      Biologist b = new Biologist();
      b.setPass_number(res.getString("Passport_number"));
      b.setName(res.getString("Name"));
      b.setContributions(res.getInt("contributions"));
      b.setCountry(res.getString("Country"));
      b.setJoin_year(res.getDate("Joined_Year"));
      bio.add(b);
    }

    return bio;
  }
}
